import openpyxl


# 考勤统计
def rans(row):
    t_rs = [0, 0, 0, 0]  # 迟到5分钟 迟到10分钟 只打一次卡 旷工
    is_name = True
    for cell_value in row:
        if is_name:
            is_name = False
        else:
            all_time = list(filter(lambda e: e != '',
                                   cell_value.split('\n') if cell_value != None and len(cell_value) != 0 else []))
            count = len(all_time)
            if count == 0:  # 未打卡
                t_rs[3] += 1
            else:
                start = all_time[0]
                t_start = tranfroms(start)
                if count == 1:  # 只打一次卡
                    t_rs[2] += 1
                    c = compare_time(t_start)
                    statistics(c, t_rs)
                else:  # 打多次卡
                    end = all_time[-1]
                    t_end = tranfroms(end)
                    if t_end[0] - t_start[0] < 4:
                        t_rs[2] += 1  # 记录只打一次卡
                        if t_end[0] < 12:  # 判断打的上班卡
                            c = compare_time(t_start)
                            statistics(c, t_rs)
                        else:  # 打的下班卡
                            c = compare_time(t_end)
                            statistics(c, t_rs)
                    else:
                        a = compare_time(t_start)
                        statistics(a, t_rs)
                        p = compare_time(t_end)
                        statistics(p, t_rs)

    return t_rs


def statistics(c, t_rs):
    if c > 5 and c < 10:
        t_rs[0] += 1
    elif c >= 10:
        t_rs[1] += 1


def tranfroms(tmp_time):
    hour, minute = tmp_time.split(':')
    sum_minute = int(hour) * 60 + int(minute)
    return [int(hour), int(minute), int(sum_minute)]


# 判断 迟到 早退
def compare_time(tmp_time):
    if tmp_time[0] < 12:  # 打的上班卡
        c = tmp_time[2] - 9 * 60
    else:  # 打的下班卡
        c = 18 * 60 - tmp_time[2]
    return c if c > 0 else 0


if __name__ == "__main__":
    workbook = openpyxl.load_workbook('kaoqin.xlsx')
    sheet = workbook.active
    max_row = sheet.max_row
    is_frist = True
    for row in sheet.iter_rows(values_only=True):  # 读取行
        if is_frist:
            is_frist = False
        else:
            print(
                f"{row[0]}：迟到5分钟{rans(row)[0]}次，迟到10分钟{rans(row)[1]}次，迟到只打卡一次{rans(row)[2]}次，旷工{rans(row)[3]}次")
